﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gasco.DataAccess;
using System.Data.Linq;

namespace Gasco.Business.Services
{
    public class CityService : ICityService
    {
        #region ICountryService Members

        public IList<city> SearchCitiesByCountry(string keyword,int pageSize, int pageNumber,string sortBy,string sortDirection,int countryId)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from c in db.cities
                         where (c.name.Contains(keyword)||c.country.name.Contains(keyword))&& (c.country_id == countryId)
                         select c;

            if(sortDirection.ToLower() == "descending")
                sortBy = String.Format("{0} DESC", sortBy);

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).SortBy<city>(sortBy).ToList<city>();
        }

        public IList<city> GetCitiesByCountry(int countryId)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from c in db.cities
                         where c.country_id == countryId
                         select c;

            return result.ToList<city>();
        }

        public int CountGetAll()
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            return db.cities.Count();
        }

        public int CountGetCitiesByCountry(int countryId)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from c in db.cities
                         where c.country_id == countryId
                         select c;
            return result.Count();
        }

        public city GetCity(int id)
        {
            using (GascoDBDataContext db = DatabaseHelper.GetData())
            {
                db.DeferredLoadingEnabled = false;

                var result = from c in db.cities
                             where c.id == id
                             select c;

                city entity = result.Single<city>();
                entity.SetAsChangeTrackingRoot(true);
                return entity;
            }
        }

        public city AddCity(city c)
        {
            c.create_date = DateTime.Now;
            c.update_date = DateTime.Now;
            return DatabaseHelper.Insert<city>(c);
        }

        public void UpdateCity(Gasco.DataAccess.city c)
        {
            c.update_date = DateTime.Now;
            DatabaseHelper.Update<city>(c);
        }

        public void DeleteCity(Gasco.DataAccess.city c)
        {
            DatabaseHelper.Delete<city>(c);
        }

        public List<city> SearchCities(string keyword, int pageSize, int pageNumber, string sortBy, string sortDirection)
        {
            var db = DatabaseHelper.GetData();
            IQueryable<city> result = null;

            if (String.IsNullOrEmpty(keyword))
            {
                result = from c in db.cities
                         select c;
            }
            else
            {
                result = from c in db.cities
                         where (c.country.name.Contains(keyword) || c.name.Contains(keyword))
                         select c;
            }

            if (sortDirection.ToLower() == "descending")
                sortBy = String.Format("{0} DESC", sortBy);

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).SortBy<city>(sortBy).ToList<city>();

        }

        public int CountSearchCities(string keyword)
        {
            var db = DatabaseHelper.GetData();
            IQueryable<city> result = null;

            if (String.IsNullOrEmpty(keyword))
            {
                result = from c in db.cities
                         select c;
            }
            else
            {
                result = from c in db.cities
                         where (c.country.name.Contains(keyword) || c.name.Contains(keyword))
                         select c;
            }
            return result.Count();                  
        }

        #endregion
    }
}
